CREATE PROCEDURE [HaOnAzure].InsertOrUpdateEndpointOnHA
	@CertificateThumbprint NVARCHAR(255),
	@SubscriptionId UNIQUEIDENTIFIER, 
	@ServiceName NVARCHAR(255), 
	@DatabaseName SYSNAME, 
	@EndPointName NVARCHAR(255), 
	@Port INT,  
	@VIP NVARCHAR(64),
	@LocalPort INT = 1433,
	@DeploymentSlot NVARCHAR(255) = 'Production', 
	@Protocol NVARCHAR(64) = 'tcp' 
AS
	DECLARE @AccountName NVARCHAR(255);
	DECLARE @SharedKey NVARCHAR(4000);

	SELECT TOP 1 @AccountName = AccountName, @SharedKey = SharedKey 
	FROM [HAOnAzure].[Credential] 
	WHERE IsMaster = 1;

	IF @AccountName IS NULL OR @SharedKey IS NULL
		THROW 53000, 'Default credential not found. Add it and try again.', 1;
	
	--------------- Check for Azure table existence ------------------
	IF NOT EXISTS(SELECT * FROM [SQL2AzureCLR].[Azure].ListTables(@AccountName, @SharedKey, 1, NULL) WHERE Name = 'HaOnAzureEndpoint')
	BEGIN
		PRINT 'Azure table not found... creating'
		EXEC [SQL2AzureCLR].[Azure].CreateTable @AccountName, @SharedKey, 1, 'HaOnAzureEndpoint';
		PRINT 'Azure table HaOnAzureEndpoint created in account ' + @AccountName
	END
	-------------- End Check for Azure table existence ----------------
	DECLARE @xml XML;

	SET @xml = 
	N'<row> ' 
		+ N'<CertificateThumbprint>' + [SQL2AzureCLR].[Azure].[ToXmlString](@CertificateThumbprint) + N'</CertificateThumbprint>' 
		+ N'<SubscriptionId>' + [SQL2AzureCLR].[Azure].[ToXmlString](@SubscriptionId) + N'</SubscriptionId>' 
		+ N'<ServiceName>' + [SQL2AzureCLR].[Azure].[ToXmlString](@ServiceName) + N'</ServiceName>' 
		+ N'<Port>' + [SQL2AzureCLR].[Azure].[ToXmlInt64](@Port) + N'</Port>' 
		+ N'<VIP>' + [SQL2AzureCLR].[Azure].[ToXmlString](@VIP) + N'</VIP>' 
		+ N'<LocalPort>' + [SQL2AzureCLR].[Azure].[ToXmlInt64](@LocalPort) + N'</LocalPort>' 
		+ N'<DeploymentSlot>' + [SQL2AzureCLR].[Azure].[ToXmlString](@DeploymentSlot) + N'</DeploymentSlot>' 
		+ N'<Protocol>' + [SQL2AzureCLR].[Azure].[ToXmlString](@Protocol) + N'</Protocol>' 
	+ N'</row>';

	--PRINT @stmt;

	SELECT @xml;

	EXEC [SQL2AzureCLR].[Azure].[InsertOrReplaceEntity]	
		@AccountName, @SharedKey, 1,
		'HaOnAzureEndpoint', 
		@DatabaseName,
		@EndPointName,
		@xml;
GO